﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace GenDB
{
    public partial class Form1 : Form
    {
        private string connStr = "";
        private int count = 0;

        public Form1()
        {
            InitializeComponent();
            this.btnClose.Click += new EventHandler(btnClose_Click);
            this.btnReload.Click += new EventHandler(btnReload_Click);
            this.btnGenerate.Click += new EventHandler(btnGenerate_Click);

            Init();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Init();
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            if (this.cmbFilename.Text.Length > 0)
            {
                TableInfo table = ClExcel.GetTableInfo(this.txtPath.Text + this.cmbFilename.Text);
                if (table != null)
                {
                    using (SqlConnection cnn = new SqlConnection(this.connStr))
                    {
                        cnn.Open();
                        List<string> lstCMD = table.GetSQLTable();

                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = cnn;

                        for (int i = 0; i < lstCMD.Count; i++)
                        {
                            cmd.CommandText = lstCMD[i];
                            try
                            {
                                cmd.ExecuteNonQuery();
                                count++;
                                this.lblStatus.Text = "Finish task " + count.ToString();
                            }
                            catch (Exception ex)
                            {
                                cmd.Dispose();
                                cnn.Close();
                                MessageBox.Show(ex.Message);
                                return;
                            }
                        }

                        cmd.Dispose();
                        cnn.Close();
                    }
                }
                else
                    MessageBox.Show("table is null");
            }
            else
                MessageBox.Show("Select excel file");
        }

        private void Init()
        {
            try
            {
                this.cmbFilename.Items.Clear();

                string appPath = Application.StartupPath;
                if (appPath.EndsWith("\\") == false)
                    appPath += "\\";
                string[] sTmp = File.ReadAllLines(appPath + "config.txt");
                this.connStr = sTmp[0];

                if (sTmp[1].EndsWith("\\") == false)
                    sTmp[1] += "\\";
                this.txtPath.Text = sTmp[1];

                DirectoryInfo di = new DirectoryInfo(sTmp[1]);
                FileInfo[] arrFile = di.GetFiles();
                for (int i = 0; i < arrFile.Length; i++)
                {
                    if (arrFile[i].Extension == ".xls" || arrFile[i].Extension == ".xlsx")
                    {
                        this.cmbFilename.Items.Add(GetFilenameOnly(arrFile[i].FullName));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in Init() Function:\r\n" + ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        private string GetFilenameOnly(string path)
        {
            int i = path.LastIndexOf("\\");
            return path.Substring(i + 1);
        }
    }
}
